package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.mysql.jdbc.StringUtils;
import cn.gson.crm.model.entity.Position;

public class PositionDao {

	private int pageSize = 5;

	/**
	 * 根据id查询
	 * @throws SQLException
	 * 
	 */
	public Position getPositionById(int status, String pid) throws SQLException {
		String sql = "select * from position where status = ? and p_id = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, status, pid);
		Position position = null;
		if (data != null) {
			position = new Position();
			position = this.buildPosition(data);
		}
		return position;

	}

	/**
	 * 通过名字查询部门
	 * @param pname
	 * @return
	 * @throws SQLException
	 */

	public List<Position> getPositionListByType(int id) throws SQLException{
		String sql = "select * from position where p_id = ? order by p_id desc";
		return queryPositionList(sql, id);
	}
	
		/**
		 * 根据id查询职位信息
		 * @throws SQLException 
		 * 
		 */
	public Position getPositionById(String pid) throws SQLException{
	String sql = "select * from position where p_id = ?";
	Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, pid);
		Position position  = null ;
		if (data != null) {
			position = new Position();
			position = this.buildPosition(data);
		}
		return position;
	}
		
	/**
	 * 通过名字查询职位信息
	 * @param pname
	 * @return
	 * @throws SQLException
	 */
	public Position getPositionByName(String pname) throws SQLException {
		String sql = "select * from position where p_name = ?";
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, pname);
		Position position = null;
		if (data != null) {
			position = new Position();
			position = this.buildPosition(data);
		}
		return position;
	}
	
		/**
		 * 保存职位信息
		 * @throws SQLException 
		 */
	public boolean savaPosition(Position po) throws SQLException{
			String sql = "insert into `position`( `p_name`, `p_discrible`) values ( ?,? )";
			return Jdbc.getInstace().executeUpdate(sql, po.getPname().trim(),po.getPdiscrible().trim());		
	} 
	
		/**
		 * 删除职位
		 * @throws SQLException 
		 * 
		 */
	public boolean deletePositionById(String pid) throws SQLException{
		String sql =  "delete from position where p_id = ? ";
		return Jdbc.getInstace().executeUpdate(sql, pid);
	}
	/**
	 * 获取职位列表
	 * 全部的职位
	 * @throws SQLException
	 */
	public List<Position> getPositionList(int status ) throws SQLException {
		String sql = "select * from position where status = ? order by p_id desc";
		List<Map<String, Object>> dataList = Jdbc.getInstace().executeQueryForList(sql,status);
		return buildPosition(dataList);
		}

	/**
	 * 获取除了自身之外的其他职位
	 * 
	 * @throws SQLException
	 */
	public List<Position> getPositionNotContainSelf(int status,String pid) throws SQLException {
		String sql = "select * from position where status = ? and p_id != ?  order by p_id desc ";
		List<Map<String, Object>> dataList = Jdbc.getInstace().executeQueryForList(sql,status, pid);
		return buildPosition(dataList);
	}


	/**
	 * 修改职位信息
	 * @throws SQLException
	 */
	public boolean changePosition(Position po) throws SQLException {
		String sql = "update  `position` set `p_name`=?,`p_discrible` =? where `p_id`=? ";
		boolean judge = Jdbc.getInstace().executeUpdate(sql, po.getPname().trim(), po.getPdiscrible().trim(),po.getId());
		return judge;
	}
	/**
	 * 修改职位状态
	 * 在网页上体现删除或创建
	 * @param dept
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public Boolean changePosition(Position position,Integer status) throws SQLException {
		String sql = "update  position set p_name = ? ,p_discrible =?,status=? where p_id =? ;";
		boolean judge = Jdbc.getInstace().executeUpdate(sql, position.getPname().trim(), position.getPdiscrible().trim(),
				status,position.getId());
		return judge;
	}
	
	/**
	 * 删除职位
	 * @throws SQLException
	 */
	public boolean deletePositionById( int status,String pid) throws SQLException {
		String sql = "update  position set status =?  where p_id = ? ";
		return Jdbc.getInstace().executeUpdate(sql,status, pid);
	}

	//组装
	private List<Position> buildPosition(List<Map<String, Object>> dataList) throws SQLException {
		List<Position> rows = null;
		if (dataList != null && dataList.size() > 0) {
			rows = new ArrayList<>();
			for (Map<String, Object> data : dataList) {
				rows.add(buildPosition(data));
			}
		}
		return rows;
	}
	
	/**
	 * 创建
	 * @param data
	 * @return
	 */
	private Position buildPosition(Map<String, Object> data) {
		Position position = new Position();
		position.setId(Long.parseLong(data.get("p_id").toString()));
		position.setPname((String) data.get("p_name"));
		position.setPdiscrible((String) data.get("p_discrible"));
		int s = Integer.parseInt(data.get("status").toString());
		position.setStatus(s);
		return position;
	}

	// 构建参数和sql
	private String buildSqlAndParams(List<Object> params, String sql, String pname, String pdiscrible,int status) {
		params.add(status);
		if (!StringUtils.isNullOrEmpty(pname)) {
			sql += " and p_name like ? ";
			params.add("%" + pname.trim() + "%");
		}
		if (!StringUtils.isNullOrEmpty(pdiscrible)) {
			sql += "and p_discrible like ? ";
			params.add("%" + pdiscrible.trim() + "%");
		}
		return sql;
	}

	private List<Position> queryPositionList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<Position> positionList = null;
		if (listData != null && listData.size() > 0) {
			positionList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				positionList.add(buildPosition(data));
			}
		}
		return positionList;
	}

	/**
	 * 带条件的分页查询
	 * @param page
	 * @return
	 * @throws SQLException
	 */
	public List<Position> getPagePositionListByType(String pname, String pdiscrible,int status,int page) throws Exception {
		int start = (page - 1) * pageSize;
		String sql = "select * from position where status = ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, pname, pdiscrible, status);
		sql += " order by p_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryPositionList(sql, params.toArray());
	}

	/**
	 * 获取总页数
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(int status) throws Exception {
		String sql = "select count(*) total from position";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, status);
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

	/**
	 * 搜索总页数
	 * @return
	 * @throws SQLException
	 */

	public int getTotalPage(int status,String pname, String pdiscrible) throws Exception {
		String sql = "select count(*) total from position where status= ? ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, pname, pdiscrible, status);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}

}
